/**
 * 数据库迁移系统
 * 用于管理数据库结构的版本控制和增量更新
 */

import { getDB } from "../index.js";
import { readFileSync, readdirSync } from "fs";
import { join, dirname } from "path";
import { fileURLToPath } from "url";

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

// 迁移版本表
const MIGRATIONS_TABLE = "schema_migrations";

/**
 * 初始化迁移表
 */
export function initMigrationsTable() {
  const db = getDB();
  db.exec(`
    CREATE TABLE IF NOT EXISTS ${MIGRATIONS_TABLE} (
      version TEXT PRIMARY KEY,
      applied_at TEXT DEFAULT (datetime('now'))
    )
  `);
}

/**
 * 获取已应用的迁移版本列表
 */
export function getAppliedMigrations() {
  const db = getDB();
  initMigrationsTable();

  const stmt = db.prepare(
    `SELECT version FROM ${MIGRATIONS_TABLE} ORDER BY version`
  );
  return stmt.all().map((row) => row.version);
}

/**
 * 记录迁移版本
 */
export function recordMigration(version) {
  const db = getDB();
  const stmt = db.prepare(
    `INSERT OR IGNORE INTO ${MIGRATIONS_TABLE} (version) VALUES (?)`
  );
  stmt.run(version);
}

/**
 * 执行迁移 SQL 文件
 */
export function executeMigration(version, sql) {
  const db = getDB();

  try {
    // 开始事务
    db.exec("BEGIN TRANSACTION");

    // 执行 SQL
    db.exec(sql);

    // 记录迁移版本
    recordMigration(version);

    // 提交事务
    db.exec("COMMIT");

    console.log(`✓ 迁移 ${version} 执行成功`);
    return true;
  } catch (error) {
    // 回滚事务
    db.exec("ROLLBACK");
    console.error(`✗ 迁移 ${version} 执行失败:`, error.message);
    throw error;
  }
}

/**
 * 读取迁移文件
 */
export function readMigrationFile(filename) {
  const filePath = join(__dirname, filename);
  return readFileSync(filePath, "utf-8");
}

/**
 * 获取所有迁移文件
 */
export function getMigrationFiles() {
  const files = readdirSync(__dirname);
  return files
    .filter(
      (file) => file.endsWith(".sql") && file !== "add_ad_and_app_config.sql"
    )
    .sort(); // 按文件名排序
}

/**
 * 运行所有未应用的迁移
 */
export function runMigrations() {
  console.log("开始执行数据库迁移...");

  initMigrationsTable();
  const appliedMigrations = getAppliedMigrations();
  const migrationFiles = getMigrationFiles();

  let appliedCount = 0;

  for (const file of migrationFiles) {
    // 从文件名提取版本号（例如：001_add_user_table.sql -> 001_add_user_table）
    const version = file.replace(".sql", "");

    if (!appliedMigrations.includes(version)) {
      console.log(`执行迁移: ${file}`);
      const sql = readMigrationFile(file);
      executeMigration(version, sql);
      appliedCount++;
    } else {
      console.log(`跳过已应用的迁移: ${file}`);
    }
  }

  if (appliedCount === 0) {
    console.log("✓ 所有迁移已是最新版本");
  } else {
    console.log(`✓ 共执行 ${appliedCount} 个迁移`);
  }
}

/**
 * 获取当前数据库版本
 */
export function getCurrentVersion() {
  const appliedMigrations = getAppliedMigrations();
  return appliedMigrations.length > 0
    ? appliedMigrations[appliedMigrations.length - 1]
    : "initial";
}
